/**
 * @file database_initializer.cpp
 * @brief 数据库初始化器实现 - 负责检查和创建数据库表结构
 * @author 29108
 * @date 2025/7/21
 * @version 1.0
 */

#include "core_services/auth_service/database_initializer.h"
#include "common/logger/logger.h"
#include <fstream>
#include <sstream>
#include <chrono>
#include <algorithm>

namespace core_services {
    namespace auth_service {

        /**
         * @brief 构造函数
         * @param mysql_pool MySQL连接池
         */
        DatabaseInitializer::DatabaseInitializer(std::shared_ptr<common::database::MySQLPool> mysql_pool)
            : mysql_pool_(mysql_pool) {
            initializeTableDefinitions();
        }

        /**
         * @brief 初始化数据库
         * @param force_recreate 是否强制重新创建表（危险操作）
         * @return 初始化结果
         */
        InitializationResult DatabaseInitializer::initializeDatabase(bool force_recreate) {
            auto start_time = std::chrono::high_resolution_clock::now();
            InitializationResult result;

            try {
                logInitialization("=== 开始数据库初始化 ===");

                // 1. 检查MySQL连接
                if (!mysql_pool_ || !mysql_pool_->isRunning()) {
                    result.errors.push_back("MySQL连接池未初始化或未运行");
                    return result;
                }

                // 2. 创建数据库（如果不存在）
                if (!createDatabaseIfNotExists("game_microservices")) {
                    result.errors.push_back("创建数据库失败");
                    return result;
                }

                // 3. 按依赖顺序获取表创建列表
                std::vector<std::string> table_creation_order = getTableCreationOrder();

                // 4. 临时禁用外键检查（确保表创建顺序不受影响）
                logInitialization("临时禁用外键检查");
                executeSQL("SET FOREIGN_KEY_CHECKS = 0");

                // 5. 检查现有表和创建缺失的表
                for (const std::string& table_name : table_creation_order) {
                    const auto& table_info = table_definitions_.at(table_name);

                    if (checkTableExists(table_name)) {
                        result.existing_tables.push_back(table_name);
                        logInitialization("表 " + table_name + " 已存在");

                        if (force_recreate) {
                            logInitialization("强制重新创建表: " + table_name, "WARNING");
                            if (!executeSQL("DROP TABLE IF EXISTS " + table_name)) {
                                result.errors.push_back("删除表 " + table_name + " 失败");
                                continue;
                            }
                        } else {
                            // 验证表结构
                            if (!validateTableStructure(table_name)) {
                                result.errors.push_back("表 " + table_name + " 结构验证失败");
                            }
                            continue;
                        }
                    }

                    // 5. 创建缺失的表
                    if (!checkTableExists(table_name) || force_recreate) {
                        logInitialization("创建表: " + table_name);
                        if (createTable(table_info)) {
                            result.created_tables.push_back(table_name);
                            logInitialization("表 " + table_name + " 创建成功");
                        } else {
                            result.errors.push_back("创建表 " + table_name + " 失败");
                        }
                    }
                }

                // 6. 重新启用外键检查
                logInitialization("重新启用外键检查");
                executeSQL("SET FOREIGN_KEY_CHECKS = 1");

                // 7. 插入初始数据（仅在有新表创建时）
                if (!result.created_tables.empty()) {
                    logInitialization("插入初始数据");
                    if (!insertInitialData()) {
                        result.errors.push_back("插入初始数据失败");
                    }
                }

                // 8. 更新数据库版本
                if (!updateDatabaseVersion("1.0.0")) {
                    result.errors.push_back("更新数据库版本失败");
                }

                result.success = result.errors.empty();

                auto end_time = std::chrono::high_resolution_clock::now();
                result.execution_time = std::chrono::duration_cast<std::chrono::milliseconds>(end_time - start_time);

                if (result.success) {
                    logInitialization("=== 数据库初始化完成 ===");
                    logInitialization("创建表数量: " + std::to_string(result.created_tables.size()));
                    logInitialization("现有表数量: " + std::to_string(result.existing_tables.size()));
                    logInitialization("执行时间: " + std::to_string(result.execution_time.count()) + "ms");
                } else {
                    logInitialization("=== 数据库初始化失败 ===", "ERROR");
                    for (const auto& error : result.errors) {
                        logInitialization("错误: " + error, "ERROR");
                    }
                }

            } catch (const std::exception& e) {
                result.success = false;
                result.errors.push_back("数据库初始化异常: " + std::string(e.what()));
                logInitialization("数据库初始化异常: " + std::string(e.what()), "ERROR");
            }

            return result;
        }

        /**
         * @brief 检查数据库表是否存在
         * @param table_name 表名
         * @return 存在返回true
         */
        bool DatabaseInitializer::checkTableExists(const std::string& table_name) {
            try {
                logInitialization("检查表是否存在: " + table_name);

                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    logInitialization("无法获取MySQL连接", "ERROR");
                    return false;
                }

                // 使用简单的SQL查询，避免预处理语句的复杂性
                std::string sql = "SELECT COUNT(*) FROM information_schema.tables "
                                 "WHERE table_schema = 'game_microservices' AND table_name = '" + table_name + "'";

                logInitialization("执行SQL: " + sql);

                auto result = mysql_conn->executeQuery(sql);

                if (result && result->next()) {
                    int count = result->getInt(1);
                    bool exists = count > 0;
                    logInitialization("表 " + table_name + " 检查结果: " + (exists ? "存在" : "不存在"));

                    return exists;
                }

                logInitialization("表 " + table_name + " 查询结果为空", "WARNING");
                return false;

            } catch (const std::exception& e) {
                logInitialization("检查表 " + table_name + " 是否存在时发生异常: " + std::string(e.what()), "ERROR");
                return false;
            }
        }

        /**
         * @brief 检查所有必需表是否存在
         * @return 所有必需表都存在返回true
         */
        bool DatabaseInitializer::checkAllRequiredTablesExist() {
            std::vector<std::string> table_order = getTableCreationOrder();

            for (const std::string& table_name : table_order) {
                const auto& table_info = table_definitions_.at(table_name);
                if (table_info.required && !checkTableExists(table_name)) {
                    return false;
                }
            }
            return true;
        }

        /**
         * @brief 获取缺失的表列表
         * @return 缺失的表名列表（按依赖顺序排列）
         */
        std::vector<std::string> DatabaseInitializer::getMissingTables() {
            std::vector<std::string> missing_tables;
            std::vector<std::string> table_order = getTableCreationOrder();

            for (const std::string& table_name : table_order) {
                if (!checkTableExists(table_name)) {
                    missing_tables.push_back(table_name);
                }
            }

            return missing_tables;
        }

        /**
         * @brief 创建单个表
         * @param table_info 表信息
         * @return 创建成功返回true
         */
        bool DatabaseInitializer::createTable(const TableInfo& table_info) {
            try {
                // 1. 创建表
                if (!executeSQL(table_info.create_sql)) {
                    logInitialization("创建表 " + table_info.name + " 失败", "ERROR");
                    return false;
                }

                // 2. 创建索引
                for (const auto& index_sql : table_info.indexes) {
                    if (!executeSQL(index_sql)) {
                        logInitialization("创建表 " + table_info.name + " 的索引失败: " + index_sql, "WARNING");
                        // 索引创建失败不影响表创建成功
                    }
                }

                return true;

            } catch (const std::exception& e) {
                logInitialization("创建表 " + table_info.name + " 时发生异常: " + std::string(e.what()), "ERROR");
                return false;
            }
        }

        /**
         * @brief 验证表结构
         * @param table_name 表名
         * @return 验证通过返回true
         */
        bool DatabaseInitializer::validateTableStructure(const std::string& table_name) {
            try {
                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    return false;
                }

                std::string sql = "SELECT 1 FROM " + table_name + " LIMIT 1";
                auto stmt = mysql_conn->prepareStatement(sql);
                stmt->executeQuery();

                return true;

            } catch (const std::exception& e) {
                logInitialization("验证表 " + table_name + " 结构时发生异常: " + std::string(e.what()), "WARNING");
                return false;
            }
        }

        /**
         * @brief 插入初始数据
         * @return 插入成功返回true
         */
        bool DatabaseInitializer::insertInitialData() {
            try {
                // 这里可以插入一些初始数据，比如默认角色、系统配置等
                // 目前暂时返回true，表示成功
                logInitialization("初始数据插入完成");
                return true;

            } catch (const std::exception& e) {
                logInitialization("插入初始数据时发生异常: " + std::string(e.what()), "ERROR");
                return false;
            }
        }

        /**
         * @brief 获取数据库版本
         * @return 数据库版本号
         */
        std::string DatabaseInitializer::getDatabaseVersion() {
            try {
                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    return "unknown";
                }

                // 检查版本表是否存在
                if (!checkTableExists("database_version")) {
                    return "0.0.0";
                }

                std::string sql = "SELECT version FROM database_version ORDER BY created_at DESC LIMIT 1";
                auto stmt = mysql_conn->prepareStatement(sql);
                auto result = stmt->executeQuery();

                if (result && result->next()) {
                    return result->getString(1);
                }

                return "0.0.0";

            } catch (const std::exception& e) {
                logInitialization("获取数据库版本时发生异常: " + std::string(e.what()), "ERROR");
                return "unknown";
            }
        }

        /**
         * @brief 更新数据库版本
         * @param version 新版本号
         * @return 更新成功返回true
         */
        bool DatabaseInitializer::updateDatabaseVersion(const std::string& version) {
            try {
                // 创建版本表（如果不存在）
                std::string create_version_table_sql = R"(
                    CREATE TABLE IF NOT EXISTS database_version (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        version VARCHAR(20) NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        description TEXT
                    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
                )";

                if (!executeSQL(create_version_table_sql)) {
                    return false;
                }

                // 插入新版本记录
                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    return false;
                }

                std::string sql = "INSERT INTO database_version (version, description) VALUES (?, ?)";
                auto stmt = mysql_conn->prepareStatement(sql);
                stmt->setString(1, version);
                stmt->setString(2, "Auth Service database schema version " + version);
                stmt->executeUpdate();

                logInitialization("数据库版本更新为: " + version);
                return true;

            } catch (const std::exception& e) {
                logInitialization("更新数据库版本时发生异常: " + std::string(e.what()), "ERROR");
                return false;
            }
        }

        /**
         * @brief 获取表创建的正确顺序（考虑外键依赖）
         * @return 按依赖顺序排列的表名列表
         */
        std::vector<std::string> DatabaseInitializer::getTableCreationOrder() {
            // 按照外键依赖关系定义表创建顺序
            // 被依赖的表必须先创建，依赖其他表的表后创建
            return {
                // 1. 基础表（无外键依赖）
                "users",                    // 用户基础信息表 - 被其他表引用
                "game_servers",             // 游戏服务器信息表 - 独立表

                // 2. 依赖users表的表
                "user_roles",               // 用户角色表 - 依赖users
                "user_metadata",            // 用户元数据表 - 依赖users
                "game_user_data",           // 游戏用户数据表 - 依赖users
                "game_user_achievements",   // 游戏用户成就表 - 依赖users
                "game_user_custom_data",    // 游戏用户自定义数据表 - 依赖users
                "user_sessions",            // 用户会话表 - 依赖users
                "user_devices"              // 用户设备信息表 - 依赖users
            };
        }

        /**
         * @brief 初始化表定义
         */
        void DatabaseInitializer::initializeTableDefinitions() {
            // 用户基础信息表
            table_definitions_["users"] = {
                "users",
                R"(CREATE TABLE IF NOT EXISTS users (
                    user_id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
                    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
                    email VARCHAR(255) NOT NULL UNIQUE COMMENT '邮箱地址',
                    password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希值',
                    salt VARCHAR(32) NOT NULL COMMENT '密码盐值',
                    nickname VARCHAR(100) NOT NULL COMMENT '昵称',
                    avatar_url VARCHAR(500) DEFAULT '' COMMENT '头像URL',
                    status TINYINT DEFAULT 1 COMMENT '用户状态：0-未激活，1-活跃，2-暂停，3-封禁，4-已删除',
                    online_status TINYINT DEFAULT 0 COMMENT '在线状态：0-离线，1-在线，2-离开，3-忙碌，4-隐身',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    last_login_at TIMESTAMP NULL COMMENT '最后登录时间',
                    last_login_ip VARCHAR(45) DEFAULT '' COMMENT '最后登录IP',
                    login_attempts INT DEFAULT 0 COMMENT '登录尝试次数',
                    locked_until TIMESTAMP NULL COMMENT '锁定到期时间'
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户基础信息表')",
                {
                    "CREATE INDEX idx_users_username ON users(username)",
                    "CREATE INDEX idx_users_email ON users(email)",
                    "CREATE INDEX idx_users_status ON users(status)",
                    "CREATE INDEX idx_users_online_status ON users(online_status)",
                    "CREATE INDEX idx_users_created_at ON users(created_at)",
                    "CREATE INDEX idx_users_last_login_at ON users(last_login_at)"
                },
                true,
                "用户基础信息表"
            };

            // 用户角色表
            table_definitions_["user_roles"] = {
                "user_roles",
                R"(CREATE TABLE IF NOT EXISTS user_roles (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    role_name VARCHAR(50) NOT NULL COMMENT '角色名称',
                    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '授予时间',
                    granted_by BIGINT DEFAULT 0 COMMENT '授予者用户ID',
                    UNIQUE KEY uk_user_role (user_id, role_name),
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户角色表')",
                {
                    "CREATE INDEX idx_user_roles_user_id ON user_roles(user_id)",
                    "CREATE INDEX idx_user_roles_role_name ON user_roles(role_name)"
                },
                true,
                "用户角色表"
            };

            // 用户元数据表
            table_definitions_["user_metadata"] = {
                "user_metadata",
                R"(CREATE TABLE IF NOT EXISTS user_metadata (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    meta_key VARCHAR(100) NOT NULL COMMENT '元数据键',
                    meta_value TEXT COMMENT '元数据值',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    UNIQUE KEY uk_user_meta (user_id, meta_key),
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户元数据表')",
                {
                    "CREATE INDEX idx_user_metadata_user_id ON user_metadata(user_id)",
                    "CREATE INDEX idx_user_metadata_meta_key ON user_metadata(meta_key)"
                },
                false,
                "用户元数据表"
            };

            // 游戏用户数据表
            table_definitions_["game_user_data"] = {
                "game_user_data",
                R"(CREATE TABLE IF NOT EXISTS game_user_data (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    game_type TINYINT NOT NULL COMMENT '游戏类型：1-贪吃蛇，2-俄罗斯方块，3-象棋，4-扑克，5-RPG，6-MOBA，7-FPS，8-策略',
                    level INT DEFAULT 1 COMMENT '游戏等级',
                    experience BIGINT DEFAULT 0 COMMENT '经验值',
                    coins BIGINT DEFAULT 0 COMMENT '游戏币',
                    gems BIGINT DEFAULT 0 COMMENT '宝石数量',
                    total_games INT DEFAULT 0 COMMENT '总游戏次数',
                    wins INT DEFAULT 0 COMMENT '胜利次数',
                    losses INT DEFAULT 0 COMMENT '失败次数',
                    draws INT DEFAULT 0 COMMENT '平局次数',
                    total_playtime_seconds BIGINT DEFAULT 0 COMMENT '总游戏时间（秒）',
                    best_score BIGINT DEFAULT 0 COMMENT '最高分数',
                    last_played_at TIMESTAMP NULL COMMENT '最后游戏时间',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    UNIQUE KEY uk_user_game (user_id, game_type),
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游戏用户数据表')",
                {
                    "CREATE INDEX idx_game_user_data_user_id ON game_user_data(user_id)",
                    "CREATE INDEX idx_game_user_data_game_type ON game_user_data(game_type)",
                    "CREATE INDEX idx_game_user_data_level ON game_user_data(level)",
                    "CREATE INDEX idx_game_user_data_experience ON game_user_data(experience)",
                    "CREATE INDEX idx_game_user_data_last_played_at ON game_user_data(last_played_at)"
                },
                true,
                "游戏用户数据表"
            };

            // 游戏用户成就表
            table_definitions_["game_user_achievements"] = {
                "game_user_achievements",
                R"(CREATE TABLE IF NOT EXISTS game_user_achievements (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    game_type TINYINT NOT NULL COMMENT '游戏类型',
                    achievement_name VARCHAR(100) NOT NULL COMMENT '成就名称',
                    achieved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '获得时间',
                    UNIQUE KEY uk_user_achievement (user_id, game_type, achievement_name),
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游戏用户成就表')",
                {
                    "CREATE INDEX idx_game_user_achievements_user_id ON game_user_achievements(user_id)",
                    "CREATE INDEX idx_game_user_achievements_game_type ON game_user_achievements(game_type)",
                    "CREATE INDEX idx_game_user_achievements_achievement_name ON game_user_achievements(achievement_name)"
                },
                false,
                "游戏用户成就表"
            };

            // 游戏用户自定义数据表
            table_definitions_["game_user_custom_data"] = {
                "game_user_custom_data",
                R"(CREATE TABLE IF NOT EXISTS game_user_custom_data (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    game_type TINYINT NOT NULL COMMENT '游戏类型',
                    data_key VARCHAR(100) NOT NULL COMMENT '数据键',
                    data_value TEXT COMMENT '数据值',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                    UNIQUE KEY uk_user_game_data (user_id, game_type, data_key),
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游戏用户自定义数据表')",
                {
                    "CREATE INDEX idx_game_user_custom_data_user_id ON game_user_custom_data(user_id)",
                    "CREATE INDEX idx_game_user_custom_data_game_type ON game_user_custom_data(game_type)",
                    "CREATE INDEX idx_game_user_custom_data_data_key ON game_user_custom_data(data_key)"
                },
                false,
                "游戏用户自定义数据表"
            };

            // 游戏服务器信息表
            table_definitions_["game_servers"] = {
                "game_servers",
                R"(CREATE TABLE IF NOT EXISTS game_servers (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    server_id VARCHAR(100) NOT NULL UNIQUE COMMENT '服务器唯一标识',
                    server_name VARCHAR(200) NOT NULL COMMENT '服务器名称',
                    host VARCHAR(255) NOT NULL COMMENT '主机地址',
                    port INT NOT NULL COMMENT '端口号',
                    game_type TINYINT NOT NULL COMMENT '支持的游戏类型',
                    region VARCHAR(50) DEFAULT '' COMMENT '服务器区域',
                    current_players INT DEFAULT 0 COMMENT '当前玩家数',
                    max_players INT DEFAULT 100 COMMENT '最大玩家数',
                    cpu_usage DECIMAL(5,2) DEFAULT 0.00 COMMENT 'CPU使用率（0-100）',
                    memory_usage DECIMAL(5,2) DEFAULT 0.00 COMMENT '内存使用率（0-100）',
                    is_healthy BOOLEAN DEFAULT TRUE COMMENT '健康状态',
                    is_accepting_players BOOLEAN DEFAULT TRUE COMMENT '是否接受新玩家',
                    last_heartbeat TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '最后心跳时间',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='游戏服务器信息表')",
                {
                    "CREATE INDEX idx_game_servers_server_id ON game_servers(server_id)",
                    "CREATE INDEX idx_game_servers_game_type ON game_servers(game_type)",
                    "CREATE INDEX idx_game_servers_region ON game_servers(region)",
                    "CREATE INDEX idx_game_servers_is_healthy ON game_servers(is_healthy)",
                    "CREATE INDEX idx_game_servers_is_accepting_players ON game_servers(is_accepting_players)",
                    "CREATE INDEX idx_game_servers_last_heartbeat ON game_servers(last_heartbeat)"
                },
                true,
                "游戏服务器信息表"
            };

            // 用户会话表
            table_definitions_["user_sessions"] = {
                "user_sessions",
                R"(CREATE TABLE IF NOT EXISTS user_sessions (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    session_id VARCHAR(100) NOT NULL UNIQUE COMMENT '会话ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    device_type VARCHAR(50) DEFAULT '' COMMENT '设备类型',
                    device_id VARCHAR(200) DEFAULT '' COMMENT '设备唯一标识',
                    client_ip VARCHAR(45) DEFAULT '' COMMENT '客户端IP地址',
                    user_agent TEXT COMMENT '用户代理字符串',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                    last_activity_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '最后活动时间',
                    expires_at TIMESTAMP NOT NULL COMMENT '过期时间',
                    is_active BOOLEAN DEFAULT TRUE COMMENT '是否活跃',
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户会话表')",
                {
                    "CREATE INDEX idx_user_sessions_session_id ON user_sessions(session_id)",
                    "CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id)",
                    "CREATE INDEX idx_user_sessions_device_id ON user_sessions(device_id)",
                    "CREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at)",
                    "CREATE INDEX idx_user_sessions_is_active ON user_sessions(is_active)",
                    "CREATE INDEX idx_user_sessions_last_activity_at ON user_sessions(last_activity_at)"
                },
                true,
                "用户会话表"
            };

            // 安全事件日志表
            table_definitions_["security_events"] = {
                "security_events",
                R"(CREATE TABLE IF NOT EXISTS security_events (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT DEFAULT 0 COMMENT '用户ID（0表示系统事件）',
                    event_type VARCHAR(100) NOT NULL COMMENT '事件类型',
                    description TEXT COMMENT '事件描述',
                    client_ip VARCHAR(45) DEFAULT '' COMMENT '客户端IP',
                    user_agent TEXT COMMENT '用户代理字符串',
                    additional_data JSON COMMENT '附加数据（JSON格式）',
                    severity TINYINT DEFAULT 1 COMMENT '严重程度：1-信息，2-警告，3-错误，4-严重',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='安全事件日志表')",
                {
                    "CREATE INDEX idx_security_events_user_id ON security_events(user_id)",
                    "CREATE INDEX idx_security_events_event_type ON security_events(event_type)",
                    "CREATE INDEX idx_security_events_severity ON security_events(severity)",
                    "CREATE INDEX idx_security_events_created_at ON security_events(created_at)",
                    "CREATE INDEX idx_security_events_client_ip ON security_events(client_ip)"
                },
                false,
                "安全事件日志表"
            };

            // 用户设备信息表
            table_definitions_["user_devices"] = {
                "user_devices",
                R"(CREATE TABLE IF NOT EXISTS user_devices (
                    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
                    user_id BIGINT NOT NULL COMMENT '用户ID',
                    device_id VARCHAR(200) NOT NULL COMMENT '设备唯一标识',
                    device_name VARCHAR(200) DEFAULT '' COMMENT '设备名称',
                    device_type VARCHAR(50) DEFAULT '' COMMENT '设备类型',
                    os_name VARCHAR(100) DEFAULT '' COMMENT '操作系统名称',
                    os_version VARCHAR(100) DEFAULT '' COMMENT '操作系统版本',
                    browser_name VARCHAR(100) DEFAULT '' COMMENT '浏览器名称',
                    browser_version VARCHAR(100) DEFAULT '' COMMENT '浏览器版本',
                    screen_resolution VARCHAR(50) DEFAULT '' COMMENT '屏幕分辨率',
                    timezone VARCHAR(100) DEFAULT '' COMMENT '时区',
                    is_trusted BOOLEAN DEFAULT FALSE COMMENT '是否受信任设备',
                    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '首次见到时间',
                    last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后见到时间',
                    UNIQUE KEY uk_user_device (user_id, device_id),
                    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户设备信息表')",
                {
                    "CREATE INDEX idx_user_devices_user_id ON user_devices(user_id)",
                    "CREATE INDEX idx_user_devices_device_id ON user_devices(device_id)",
                    "CREATE INDEX idx_user_devices_device_type ON user_devices(device_type)",
                    "CREATE INDEX idx_user_devices_is_trusted ON user_devices(is_trusted)",
                    "CREATE INDEX idx_user_devices_last_seen ON user_devices(last_seen)"
                },
                false,
                "用户设备信息表"
            };
        }

        /**
         * @brief 执行SQL语句
         * @param sql SQL语句
         * @return 执行成功返回true
         */
        bool DatabaseInitializer::executeSQL(const std::string& sql) {
            try {
                logInitialization("准备执行SQL: " + sql);

                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    logInitialization("无法获取MySQL连接", "ERROR");
                    return false;
                }

                logInitialization("获取MySQL连接成功，开始执行SQL");

                // 使用executeUpdate执行DDL语句（CREATE, DROP, ALTER等）
                int affected_rows = mysql_conn->executeUpdate(sql);

                logInitialization("SQL执行成功，影响行数: " + std::to_string(affected_rows));

                return true;

            } catch (const std::exception& e) {
                logInitialization("执行SQL失败: " + std::string(e.what()) + " SQL: " + sql, "ERROR");
                return false;
            }
        }

        /**
         * @brief 执行SQL文件
         * @param file_path SQL文件路径
         * @return 执行成功返回true
         */
        bool DatabaseInitializer::executeSQLFile(const std::string& file_path) {
            try {
                std::ifstream file(file_path);
                if (!file.is_open()) {
                    logInitialization("无法打开SQL文件: " + file_path, "ERROR");
                    return false;
                }

                std::stringstream buffer;
                buffer << file.rdbuf();
                std::string sql_content = buffer.str();

                // 简单的SQL语句分割（按分号分割）
                std::stringstream ss(sql_content);
                std::string sql_statement;

                while (std::getline(ss, sql_statement, ';')) {
                    // 去除空白字符
                    sql_statement.erase(0, sql_statement.find_first_not_of(" \t\n\r"));
                    sql_statement.erase(sql_statement.find_last_not_of(" \t\n\r") + 1);

                    if (!sql_statement.empty() && sql_statement.find("--") != 0) {
                        if (!executeSQL(sql_statement + ";")) {
                            return false;
                        }
                    }
                }

                return true;

            } catch (const std::exception& e) {
                logInitialization("执行SQL文件失败: " + std::string(e.what()), "ERROR");
                return false;
            }
        }

        /**
         * @brief 创建数据库（如果不存在）
         * @param database_name 数据库名
         * @return 创建成功返回true
         */
        bool DatabaseInitializer::createDatabaseIfNotExists(const std::string& database_name) {
            try {
                logInitialization("开始创建数据库: " + database_name);

                std::string sql = "CREATE DATABASE IF NOT EXISTS " + database_name +
                                 " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

                logInitialization("执行创建数据库SQL: " + sql);
                if (!executeSQL(sql)) {
                    logInitialization("创建数据库SQL执行失败", "ERROR");
                    return false;
                }

                // 切换到目标数据库
                std::string use_sql = "USE " + database_name;
                logInitialization("执行切换数据库SQL: " + use_sql);
                if (!executeSQL(use_sql)) {
                    logInitialization("切换数据库SQL执行失败", "ERROR");
                    return false;
                }

                logInitialization("数据库 " + database_name + " 准备就绪");
                return true;

            } catch (const std::exception& e) {
                logInitialization("创建数据库失败: " + std::string(e.what()), "ERROR");
                return false;
            }
        }

        /**
         * @brief 获取表的列信息
         * @param table_name 表名
         * @return 列信息列表
         */
        std::vector<std::string> DatabaseInitializer::getTableColumns(const std::string& table_name) {
            std::vector<std::string> columns;

            try {
                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    return columns;
                }

                std::string sql = "SHOW COLUMNS FROM " + table_name;
                auto result = mysql_conn->executeQuery(sql);

                while (result && result->next()) {
                    columns.push_back(result->getString(1));
                }

            } catch (const std::exception& e) {
                logInitialization("获取表列信息失败: " + std::string(e.what()), "ERROR");
            }

            return columns;
        }

        /**
         * @brief 获取表的索引信息
         * @param table_name 表名
         * @return 索引信息列表
         */
        std::vector<std::string> DatabaseInitializer::getTableIndexes(const std::string& table_name) {
            std::vector<std::string> indexes;

            try {
                // 使用 RAII 连接管理器，确保连接自动归还
                common::database::MySQLConnectionGuard conn_guard(*mysql_pool_);
                auto mysql_conn = conn_guard.get();
                if (!mysql_conn) {
                    return indexes;
                }

                std::string sql = "SHOW INDEX FROM " + table_name;
                auto result = mysql_conn->executeQuery(sql);

                while (result && result->next()) {
                    indexes.push_back(result->getString("Key_name"));
                }

            } catch (const std::exception& e) {
                logInitialization("获取表索引信息失败: " + std::string(e.what()), "ERROR");
            }

            return indexes;
        }

        /**
         * @brief 记录初始化日志
         * @param message 日志消息
         * @param level 日志级别
         */
        void DatabaseInitializer::logInitialization(const std::string& message, const std::string& level) {
            std::string log_message = "[DatabaseInitializer] " + message;

            if (level == "ERROR") {
                LOG_ERROR(log_message);
            } else if (level == "WARNING") {
                LOG_WARNING(log_message);
            } else if (level == "DEBUG") {
                LOG_DEBUG(log_message);
            } else {
                LOG_INFO(log_message);
            }
        }

    } // namespace auth_service
} // namespace core_services
